import pandas as pd
from django.shortcuts import render, get_object_or_404, redirect
from django.contrib import messages
from sqlalchemy import create_engine
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
from insight.forms import *
from django.http import HttpResponse
# Create your views here.
from insight.models import *


statistical_method_dict = {
    'min': '最小值',
    'max': '最大值',
    'mean': '平均值',
    'std': '标准差',
    'sum': '总和',
    'median': '中位数',
    'count': '计数'
}

statistical_method_list = [
    {'method_name': 'min',
     'method_desc': '最小值'},
    {'method_name': 'max',
     'method_desc': '最大值'},
    {'method_name': 'mean',
     'method_desc': '平均值'},
    {'method_name': 'std',
     'method_desc': '标准差'},
    {'method_name': 'median',
     'method_desc': '中位数'},
]

physical_quantity_dict = {
    'current1': '大车变频器电流',
    'current2': '小车变频器电流',
    'current3': '起升变频器电流',
    'frequency1': '大车变频器频率',
    'frequency2': '小车变频器频率',
    'frequency3': '起升变频器频率',
    'power1': '大车1号伺服功率',
    'power2': '大车2号伺服功率',
    'power3': '小车1号伺服功率',
    'power4': '小车2号伺服功率',
    'power5': '起升伺服功率',
    'speed1': '大车1号伺服速度',
    'speed2': '大车2号伺服速度',
    'speed3': '小车1号伺服速度',
    'speed4': '小车2号伺服速度',
    'speed5': '起升伺服速度',
    'time_duration': '耗时',
    'torque1': '大车1号伺服扭矩',
    'torque2': '大车2号伺服扭矩',
    'torque3': '小车1号伺服扭矩',
    'torque4': '小车2号伺服扭矩',
    'torque5': '起升伺服扭矩',
    'weight': '称重',
    'x_distance': '大车位移',
    'x_power': '大车功率',
    'y_distance': '小车位移',
    'y_power': '小车功率',
    'z_distance': '起升位移',
    'z_power': '起升功率'
}

inverter_quantity_list = [
    {'quantity_name': 'current1',
     'quantity_desc': '大车变频器电流'},
    {'quantity_name': 'current2',
     'quantity_desc': '小车变频器电流'},
    {'quantity_name': 'current3',
     'quantity_desc': '起升变频器电流'},
    {'quantity_name': 'frequency1',
     'quantity_desc': '大车变频器频率'},
    {'quantity_name': 'frequency2',
     'quantity_desc': '小车变频器频率'},
    {'quantity_name': 'frequency3',
     'quantity_desc': '起升变频器频率'},
    {'quantity_name': 'power1',
     'quantity_desc': '大车变频器功率'},
    {'quantity_name': 'power2',
     'quantity_desc': '小车变频器功率'},
    {'quantity_name': 'power3',
     'quantity_desc': '起升变频器功率'},
    {'quantity_name': 'weight',
     'quantity_desc': '称重'},
]

servo_quantity_list = [
    {'quantity_name': 'torque1',
     'quantity_desc': '大车1#伺服扭矩'},
    {'quantity_name': 'torque2',
     'quantity_desc': '大车2#伺服扭矩'},
    {'quantity_name': 'torque3',
     'quantity_desc': '小车1#伺服扭矩'},
    {'quantity_name': 'torque4',
     'quantity_desc': '小车2#伺服扭矩'},
    {'quantity_name': 'torque5',
     'quantity_desc': '起升伺服扭矩'},
    {'quantity_name': 'speed1',
     'quantity_desc': '大车1#伺服速度'},
    {'quantity_name': 'speed2',
     'quantity_desc': '大车2#伺服速度'},
    {'quantity_name': 'speed3',
     'quantity_desc': '小车1#伺服速度'},
    {'quantity_name': 'speed4',
     'quantity_desc': '小车2#伺服速度'},
    {'quantity_name': 'speed5',
     'quantity_desc': '起升伺服速度'},
    {'quantity_name': 'power1',
     'quantity_desc': '大车1#伺服功率'},
    {'quantity_name': 'power2',
     'quantity_desc': '大车2#伺服功率'},
    {'quantity_name': 'power3',
     'quantity_desc': '小车1#伺服功率'},
    {'quantity_name': 'power4',
     'quantity_desc': '小车2#伺服功率'},
    {'quantity_name': 'power5',
     'quantity_desc': '起升伺服功率'},
    {'quantity_name': 'x_power',
     'quantity_desc': '大车伺服总功率'},
    {'quantity_name': 'y_power',
     'quantity_desc': '小车伺服总功率'},
    {'quantity_name': 'weight',
     'quantity_desc': '称重'}
]


def main_index(request):
    return render(request, "insight/index.html")


def station_index(request):
    stations = Station.objects.order_by('station_id')
    paginator = Paginator(stations, 10)
    page_number = request.GET.get('page', 1)
    try:
        current_page = paginator.page(page_number)
    except PageNotAnInteger:
        current_page = paginator.page(1)
    except EmptyPage:
        current_page = paginator.page(paginator.num_pages)
    context = {
        "paginator": paginator,
        "current_page": current_page
    }
    return render(request, "insight/station/index.html", context)


def feature_index(request):
    station_types = StationType.objects.all()
    robot_types = RobotType.objects.all()
    physical_quantities = PhysicalQuantity.objects.all()
    statistical_methods = StatisticalMethod.objects.all()
    data_dict = {
        "station_types": station_types,
        "robot_types": robot_types,
        "physical_quantities": physical_quantities,
        "statistical_methods": statistical_methods
    }
    return render(request, "insight/feature/index.html", data_dict)


def swap_service(request, station_id):
    station = get_object_or_404(Station, pk=station_id)
    services = SwapService.objects.filter(station_id=station_id).order_by('-start_time').all()
    paginator = Paginator(services, 10)
    page_number = request.GET.get('page', 1)
    try:
        current_page = paginator.page(page_number)
    except PageNotAnInteger:
        current_page = paginator.page(1)
    except EmptyPage:
        current_page = paginator.page(paginator.num_pages)
    context = {
        "station": station,
        "paginator": paginator,
        "current_page": current_page
    }
    return render(request, "insight/service/index.html", context)


def station_view(request, station_id):
    station = get_object_or_404(Station, pk=station_id)
    if station.drive_type == 'inverter':
        physical_quantities = inverter_quantity_list
    elif station.drive_type == 'servo':
        physical_quantities = servo_quantity_list
    else:
        physical_quantities = None
    sql = f"""
            SELECT a.*, b.stationName, c.quantityDesc, d.methodDesc
            FROM insight_fault_prediction AS a
            LEFT JOIN insight_swap_station AS b
            ON a.stationNo=b.stationNo
            LEFT JOIN insight_PHYSICAL_QUANTITY AS c
            ON a.physicalQuantity=c.quantityName
            LEFT JOIN insight_STATISTICAL_METHOD AS d
            ON a.statisticalMethod=d.methodName
            WHERE a.stationNo='{station_id}'
            ORDER BY a.feature_date DESC
            """
    predictions = FaultPrediction.objects.raw(sql)
    data_dict = {
        "station": station,
        "physical_quantities": physical_quantities,
        "statistical_methods": statistical_method_list,
        "predictions": predictions
    }
    return render(request, "insight/station/view.html", data_dict)


def service_feature(request, order_sn):
    service = get_object_or_404(SwapService, pk=order_sn)
    station = get_object_or_404(Station, pk=service.station_id)
    # service_features = SwapServiceFeature.objects.filter(order_sn=order_sn).all()
    sql = f"""
        SELECT * FROM insight_swap_service_feature
        WHERE orderSn='{order_sn}'
    """
    service_features = SwapServiceFeature.objects.raw(sql)
    data_dict = {
        "station": station,
        "service": service,
        "service_features": service_features,
    }
    return render(request, "insight/service/features.html", data_dict)


def service_feature_line(request, station_id, physical_quantity, statistical_method):
    station = get_object_or_404(Station, pk=station_id)
    engine_tencent = create_engine("mysql+pymysql://root:Qiyuan*2023@43.137.4.113:3306/inspectai")
    sql = f"""
        SELECT m.startTime AS start_time, m.featureValue AS feature_value FROM(
            SELECT a.*, b.startTime
            FROM inspectai.insight_swap_service_feature AS a
            LEFT JOIN inspectai.insight_swap_service AS b
            ON a.orderSn=b.orderSn
            WHERE a.physicalQuantity='{physical_quantity}'
                AND a.statisticalMethod='{statistical_method}'
                AND b.stationNo='{station_id}'
            ORDER BY b.startTime DESC
            LIMIT 300) AS m
        ORDER BY m.startTime ASC
    """
    df_features = pd.read_sql(sql, engine_tencent)
    feature_value_mean = round(float(df_features['feature_value'].mean()), 2)
    feature_value_std = round(float(df_features['feature_value'].std()), 2)
    upper_limit = [feature_value_mean + 3 * feature_value_std for i in range(df_features.shape[0] + 1)]
    down_limit = [feature_value_mean - 3 * feature_value_std for i in range(df_features.shape[0] + 1)]
    data_dict = {
        "station": station,
        "start_time_list": df_features['start_time'].astype(str).tolist(),
        "feature_value_list": df_features['feature_value'].astype(float).tolist(),
        "physical_quantity": physical_quantity,
        "statistical_method": statistical_method,
        "physical_quantity_desc": physical_quantity_dict[physical_quantity],
        "statistical_method_desc": statistical_method_dict[statistical_method],
        "upper_limit": upper_limit,
        "down_limit": down_limit,
        "upper_limit_value": round(upper_limit[0], 2),
        "down_limit_value": round(down_limit[0], 2)
    }
    return render(request, "insight/station/service_feature_line.html", data_dict)


def daily_feature_line(request, station_id, physical_quantity, statistical_method):
    station = get_object_or_404(Station, pk=station_id)
    engine_tencent = create_engine("mysql+pymysql://root:Qiyuan*2023@43.137.4.113:3306/inspectai")
    sql = f"""
        SELECT feature_date, featureValue
        FROM insight_daily_feature
        WHERE stationNo='{station_id}'
            AND physicalQuantity='{physical_quantity}'
            AND statisticalMethod='{statistical_method}'
        ORDER BY feature_date DESC
        LIMIT 30
    """
    df_feature = pd.read_sql(sql, engine_tencent)
    df_feature = df_feature.sort_values(['feature_date'])
    # 计算之前的平均值和方差
    feature_value_mean = round(float(df_feature['featureValue'].iloc[:-1].mean()), 3)
    feature_value_std = round(float(df_feature['featureValue'].iloc[:-1].std()), 3)
    df_feature['feature_date'] = pd.to_datetime(df_feature['feature_date'])
    df_feature = df_feature.set_index('feature_date')
    if df_feature.shape[0] > 7:
        daily_resampled = df_feature.resample('D').asfreq().reset_index()
        daily_resampled['feature_date'] = daily_resampled['feature_date'].dt.strftime('%Y-%m-%d')
        daily_resampled.loc[daily_resampled['featureValue'].notnull(), 'featureValue'] = daily_resampled.loc[
            daily_resampled['featureValue'].notnull(), 'featureValue'] .apply(lambda x: round(x, 2))
        daily_resampled = daily_resampled.fillna('null')
        upper_limit = [feature_value_mean+3*feature_value_std for i in range(daily_resampled.shape[0]+1)]
        down_limit = [feature_value_mean-3*feature_value_std for i in range(daily_resampled.shape[0]+1)]
        data_dict = {
            "station": station,
            "date_list": daily_resampled['feature_date'].tolist(),
            "feature_list": daily_resampled['featureValue'].tolist(),
            "physical_quantity": physical_quantity,
            "statistical_method": statistical_method,
            "physical_quantity_desc": physical_quantity_dict[physical_quantity],
            "statistical_method_desc": statistical_method_dict[statistical_method],
            "upper_limit": upper_limit,
            "down_limit": down_limit,
            "upper_limit_value": round(upper_limit[0], 2),
            "down_limit_value": round(down_limit[0], 2),
            "sigma": 3
        }
        return render(request, "insight/station/daily_feature_line.html", data_dict)
    else:
        close_script = """
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="UTF-8">
            <title>未查询到结果</title>
        </head>
        <body>
            <script type="text/javascript">
                // 提示用户（可选）
                alert('未能获取结果，窗口将自动关闭...');
                // 关闭当前窗口
                window.close();
            </script>
        </body>
        </html>
        """
        return HttpResponse(close_script)


def fault_prediction_index(request):
    sql = """
        SELECT a.*, b.stationName, c.quantityDesc, d.methodDesc
        FROM insight_fault_prediction AS a
        LEFT JOIN insight_swap_station AS b
        ON a.stationNo=b.stationNo
        LEFT JOIN insight_PHYSICAL_QUANTITY AS c
        ON a.physicalQuantity=c.quantityName
        LEFT JOIN insight_STATISTICAL_METHOD AS d
        ON a.statisticalMethod=d.methodName
        ORDER BY a.prediction_id DESC
        """
    predictions = FaultPrediction.objects.raw(sql)
    paginator = Paginator(predictions, 10)
    page_number = request.GET.get('page', 1)
    try:
        current_page = paginator.page(page_number)
    except PageNotAnInteger:
        current_page = paginator.page(1)
    except EmptyPage:
        current_page = paginator.page(paginator.num_pages)
    data_dict = {
        "paginator": paginator,
        "current_page": current_page
    }
    return render(request, 'insight/prediction/index.html', data_dict)


def fault_prediction_view(request, prediction_id):
    prediction = get_object_or_404(FaultPrediction, pk=prediction_id)
    station = get_object_or_404(Station, pk=prediction.station_id)
    engine_tencent = create_engine("mysql+pymysql://root:Qiyuan*2023@43.137.4.113:3306/inspectai")
    sql = f"""
            SELECT feature_date, featureValue
            FROM insight_daily_feature
            WHERE stationNo='{prediction.station_id}'
                AND physicalQuantity='{prediction.physical_quantity}'
                AND statisticalMethod='{prediction.statistical_method}'
                AND feature_date<='{prediction.feature_date}'
            ORDER BY feature_date DESC
            LIMIT 30
        """
    df_feature = pd.read_sql(sql, engine_tencent)
    df_feature = df_feature.sort_values(['feature_date'])
    # 计算之前的平均值和方差
    feature_value_mean = round(float(df_feature['featureValue'].iloc[:-1].mean()), 3)
    feature_value_std = round(float(df_feature['featureValue'].iloc[:-1].std()), 3)
    df_feature['feature_date'] = pd.to_datetime(df_feature['feature_date'])
    df_feature = df_feature.set_index('feature_date')
    daily_resampled = df_feature.resample('D').asfreq().reset_index()
    daily_resampled['feature_date'] = daily_resampled['feature_date'].dt.strftime('%Y-%m-%d')
    daily_resampled.loc[daily_resampled['featureValue'].notnull(), 'featureValue'] = daily_resampled.loc[
        daily_resampled['featureValue'].notnull(), 'featureValue'].apply(lambda x: round(x, 2))
    daily_resampled = daily_resampled.fillna('null')
    upper_limit = [feature_value_mean + prediction.sigma * feature_value_std for i in range(daily_resampled.shape[0] + 1)]
    down_limit = [feature_value_mean - prediction.sigma * feature_value_std for i in range(daily_resampled.shape[0] + 1)]
    data_dict = {
        "station": station,
        "date_list": daily_resampled['feature_date'].tolist(),
        "feature_list": daily_resampled['featureValue'].tolist(),
        "physical_quantity": prediction.physical_quantity,
        "statistical_method": prediction.statistical_method,
        "physical_quantity_desc": physical_quantity_dict[prediction.physical_quantity],
        "statistical_method_desc": statistical_method_dict[prediction.statistical_method],
        "upper_limit": upper_limit,
        "down_limit": down_limit,
        "upper_limit_value": round(upper_limit[0], 2),
        "down_limit_value": round(down_limit[0], 2),
        "sigma": int(prediction.sigma)
    }
    return render(request, "insight/station/daily_feature_line.html", data_dict)